
<?php 
	include_once("../DbHandler/DataProvider.php");
	include_once("../DbHandler/DBConsts.php");
?>
<?php
class ProductController{
	/**
	 * get product categories by parent_category_id.
	 * @param unknown $parentCategoryID
	 * @return NULL|multitype:
	 */
	public static function getProductCategoriesByParentCategoryID($parentCategoryID){
		if($parentCategoryID == null)
			return null;
		//look up the parent of the node
		$strSQL = "Select * from ".DBConsts::$CATEGORY." where parent_category_id='".$parentCategoryID."'";
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$listCategoryArray = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			//save the caterogy in this array
			$parent = array("id"=>$row['id'],"name"=>$row["name"], "bannerimage"=>$row['banner_image'], "thumbimage"=>$row['thumb_image'], "parent_category_id"=>$row['parent_category_id']);
			if($row['parent_category_id'] != ''){
				$children = ProductController::getProductCategoriesByParentCategoryID($row['id'] );
				if($children!=null){
					$parent = array("id"=>$row['id'],"name"=>$row["name"], "bannerimage"=>$row['banner_image'], "thumbimage"=>$row['thumb_image'], "parent_category_id"=>$row['parent_category_id'],"children"=>$children);
				}
			}
			array_push($listCategoryArray, $parent);
		}
		return $listCategoryArray;
	}
	
	/**
	 * get product categories
	 * @return list of product category
	 */
	public static function getProductCategories(){
		$strSQL = "Select * from ".DBConsts::$CATEGORY;
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$res = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			if($row["parent_category_id"]=='')
			{
 				$parent = array("id"=>$row['id'],"name"=>$row['name'], "bannerimage"=>$row['banner_image'], "thumbimage"=>$row['thumb_image']);
				$children = ProductController::getProductCategoriesByParentCategoryID($row['id']);
				if($children!=null){
					$parent = array("id"=>$row['id'],"name"=>$row["name"], "bannerimage"=>$row['banner_image'], "thumbimage"=>$row['thumb_image'], "children"=>$children);
				} 
				array_push($res, $parent);
			}
		}
		return $res;
	}
	
	/**
	 * get products by category id.
	 * @param unknown $categoryId
	 */
	public static function getProductsByCategoryID($categoryId){
		$strSQL = "SELECT *
					FROM ".DBConsts::$CABINET." c, ( select * FROM ".DBConsts::$CABINET_CATEGORY." cc WHERE cc.category_id=".$categoryId.") as cc
					WHERE c.code=cc.cabinet_code";
		$product = DataProvider::Query($strSQL);
		if(mysqli_num_rows($product)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($product,MYSQL_BOTH))
		{
			$array = array("code"=>$item['code'], "name"=>$item['name'], "short_name"=>$item['short_name'],"description"=>$item['description'],
					"short_description"=>$item['short_description'], "width"=>$item['width'], "height"=>$item['height'],"image"=>$item['image'],
					"retailprice"=>$item['retail_price'], "fobprice"=>$item['fob_price'],"cabinettypeid"=>$item['cabinet_type_id'],
					"isconfigurable"=>$item['is_configurable'],"gridthumbimage"=>$item['grid_thumb_image'],"listthumbimage"=>$item['list_thumb_image'],
					"specialprice"=>$item['special_price'],"membershipprice"=>$item['membership_price']);
			array_push($result, $array);
		}
		return $result;
	}
	
	/**
	 * get product by id
	 * @param unknown $id
	 * @return NULL|multitype:
	 */
	public static function getProductsByID($id){
		$strSQL = "Select * from ".DBConsts::$CABINET." where code='".$id."'";
		
		$strSQL="SELECT c.code, c.name, c.short_name, c.description, c.short_description, c.width, c.height, c.image,
			 c.retail_price, c.fob_price, c.cabinet_type_id, c.is_configurable, c.grid_thumb_image,
			 c.list_thumb_image, c.special_price, c.membership_price, c.core_price ,pc.cost_per_m2, cp.price as cabinet_part_price 
			FROM ".DBConsts::$CABINET." c, ".DBConsts::$PANEL_COAT." pc, ".DBConsts::$CABINET_PART." cp 
			WHERE c.code='".$id."' and c.panel_coating_default_id=pc.id and cp.code=c.cabinet_part_code_default";
		
		
		$result = DataProvider::Query($strSQL);
		$lstImage = ProductController::getProductImages($id);
		$lstCabinetOptionalPart = ProductController::getCabinetOptionalPart($id);
		$lstCabinetConfigurablePart = ProductController::getCabinetConfigurablePart($id);
		$lstCabinetPanelCoat = ProductController::getCabinetPanelCoat($id);
		$lstSimilarCabinet = ProductController::getSimilarProduct($id);
		if(mysqli_num_rows($result)==0)
			return null;
		$item = mysqli_fetch_array($result,MYSQL_BOTH);
		$array = array("code"=>$item['code'], "name"=>$item['name'], "short_name"=>$item['short_name'],"description"=>$item['description'],
					"short_description"=>$item['short_description'], "width"=>$item['width'], "height"=>$item['height'],"image"=>$item['image'],
					"retailprice"=>$item['retail_price'], "fobprice"=>$item['fob_price'],"cabinettypeid"=>$item['cabinet_type_id'],
					"isconfigurable"=>$item['is_configurable'],"gridthumbimage"=>$item['grid_thumb_image'],"listthumbimage"=>$item['list_thumb_image'],
					"specialprice"=>$item['special_price'],"membershipprice"=>$item['membership_price'], "coreprice"=>$item['core_price'],
					"costperm2"=>$item['cost_per_m2'], "cabinetpartprice"=>$item['cabinet_part_price'],"listimage"=>$lstImage,
				    "listcabinetoptionalpart"=>$lstCabinetOptionalPart,  "listcabinetconfigurablepart"=>$lstCabinetConfigurablePart, 
				 	"lstcabinetpanelcoat"=>$lstCabinetPanelCoat, "lstsimilarcabinet"=>$lstSimilarCabinet);
		return $array;
	}
	/**
	 * get products by category id.
	 * @param unknown $categoryId
	 * @param unknown $offset
	 * @param unknown $count
	 * @return NULL|multitype:
	 */
	public static function getProductsByCategoryLimit($categoryId, $page, $count)
	{
	$offset = 0;
		if($page <= 0){
			return null;
		} else if($page > 0){
			$offset = ($page - 1) * $count;
		}
		$strSQL = "SELECT * 
					FROM ".DBConsts::$CABINET." c, ( select * FROM ".DBConsts::$CABINET_CATEGORY." cc WHERE cc.category_id=".$categoryId.") as cc
					WHERE c.code=cc.cabinet_code
					LIMIT ".$offset.", ".$count."";
		$products = DataProvider::Query($strSQL);		
		if(mysqli_num_rows($products)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($products,MYSQL_BOTH))
		{
			$array = array("code"=>$item['code'], "name"=>$item['name'], "short_name"=>$item['short_name'],"description"=>$item['description'],
					"short_description"=>$item['short_description'], "width"=>$item['width'], "height"=>$item['height'],"image"=>$item['image'],
					"retailprice"=>$item['retail_price'], "fobprice"=>$item['fob_price'],"cabinettypeid"=>$item['cabinet_type_id'],
					"isconfigurable"=>$item['is_configurable'],"gridthumbimage"=>$item['grid_thumb_image'],"listthumbimage"=>$item['list_thumb_image'],
					"specialprice"=>$item['special_price'],"membershipprice"=>$item['membership_price']);
			array_push($result, $array);
		}
		return $result;
	}
	
	public static function getEachPanel($cabinetCode, $panelId){
		$strSQL = " SELECT cabinet_code,id,title,path_medium,path_small,path_large, path_large as tmp,panel_coat_id,is_main_image
					FROM ".DBConsts::$CABINET_IMAGE." 
					WHERE cabinet_code='".$cabinetCode."' and panel_coat_id='".$panelId."'";
		
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$listImageArray = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			$tmp = "{gallery: 'gal%d', smallimage: '%s',largeimage: '%s'}";
			$tmp = sprintf($tmp, $panelId , $row['path_medium'], $row['path_large']);
			$imageArray = array("cabinetcode"=>$row['cabinet_code'], "id"=>$row['id'], "title"=>$row['title'], "pathmedium"=>$row['path_medium'],
					"ismainimage"=>$row['is_main_image'], "panelcoatid"=>$row['panel_coat_id'],
					"pathsmall"=>$row['path_small'], "pathlarge"=>$row['path_large'], "tmp"=>$tmp );
			array_push($listImageArray, $imageArray);
		}
		return $listImageArray;
	}
	/**
	 * get images of a product by product id.
	 * @param unknown $productID
	 * @return NULL|multitype:
	 */
	public static function getProductImages($cabinetCode){
		$strSQL = "Select cabinet_code,min(id) as id,title,path_medium,path_small,path_large,panel_coat_id,is_main_image 
				from ".DBConsts::$CABINET_IMAGE."  where cabinet_code='".$cabinetCode."' group by panel_coat_id";
		
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$listImageArray = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			
			//get group image for each panel
			$lstEachPanel = ProductController::getEachPanel($row['cabinet_code'],$row['panel_coat_id']);
			
			$imageArray = array("cabinetcode"=>$row['cabinet_code'], "id"=>$row['id'], "title"=>$row['title'], "pathmedium"=>$row['path_medium'],
					 "ismainimage"=>$row['is_main_image'], "panelcoatid"=>$row['panel_coat_id'],
					 "pathsmall"=>$row['path_small'], "pathlarge"=>$row['path_large'], "eachpanel"=>$lstEachPanel);
			array_push($listImageArray, $imageArray);
		}
		return $listImageArray;
	}
	
	/**
	 * get infomation of a cabinet configurable part by cabinet code.
	 * @param unknown $cabinetCode
	 * @return NULL|multitype:
	 */
	public static function getCabinetConfigurablePart($cabinetCode){
		$strSQL="SELECT cp.code,cp.name,cp.description, cp.price, cp.thumb_image 
	 		FROM ".DBConsts::$CABINET_PART." cp, 
	 		(SELECT DISTINCT cp.code FROM ".DBConsts::$CABINET_PART." cp, ".DBConsts::$CABINET_CONFIGURABLE_PART." ccp, ".DBConsts::$CABINET_CONFIGURABLE_PART_OPTION." ccpo WHERE cp.code=ccpo.cabinet_part_code and ccpo.cabinet_configurable_part_id=ccp.id and ccp.cabinet_code='".$cabinetCode."') as cabinet_part_id 
	 		WHERE cp.code=cabinet_part_id.code";
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$listImageArray = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			$imageArray = array("code"=>$row['code'], "name"=>$row['name'], "description"=>$row['description'],
					"thumb_image"=>$row['thumb_image'], "price"=>$row['price']);
			array_push($listImageArray, $imageArray);
		}
		return $listImageArray;
	}
	
	/**
	 * get similar product by cabinet code.
	 * @param unknown $cabinetCode
	 */
	public static function getSimilarProduct($cabinetCode){
		$strSQL = "SELECT c.code,c.name, c.grid_thumb_image, c.special_price, c.retail_price, cc1.category_id
		FROM ".DBConsts::$CABINET." c, (SELECT * FROM ".DBConsts::$CABINET_RELATED." cr WHERE cr.cabinet_code='".$cabinetCode."') as cr,
		(Select cc.cabinet_code, min(cc.category_id) as category_id from ".DBConsts::$CABINET_CATEGORY." cc group by cc.cabinet_code) as cc1
		WHERE cr.related_cabinet_code=c.code and c.code=cc1.cabinet_code";
		
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$listImageArray = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			$imageArray = array("code"=>$row['code'], "name"=>$row['name'], "gridthumbimage"=>$row['grid_thumb_image'],
					"retailprice"=>$row['retail_price'],"specialprice"=>$row['special_price'],"categoryid"=>$row['category_id']);
			array_push($listImageArray, $imageArray);
		}
		return $listImageArray;
	}
	
	/**
	 * get infomation of a cabinet panel coat by cabinet code.
	 * @param unknown $cabinetCode
	 * @return NULL|multitype:
	 */
	public static function getCabinetPanelCoat($cabinetCode){
		$strSQL = "SELECT pc.id, pc.name, pc.panel_coat_image 
					FROM ".DBConsts::$PANEL_COAT." pc, ".DBConsts::$CABINET_AVAILABLE_COATING." cac
					WHERE cac.cabinet_code='TD900' and cac.coating_id=pc.id";
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$listImageArray = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			$imageArray = array("id"=>$row['id'], "name"=>$row['name'], "panelcoatimage"=>$row['panel_coat_image']);
			array_push($listImageArray, $imageArray);
		}
		return $listImageArray;
	}
	/**
	 * get images of a cabinet part by cabinet code.
	 * @param unknown $cabinetCode
	 * @return NULL|multitype:
	 */
	public static function getCabinetOptionalPart($cabinetCode){
		//$strSQL = "Select * from ".DBConsts::$CABINET_IMAGE." where cabinet_code='".$cabinetCode."'";
		$strSQL = "SELECT cp.code, cp.name, cp.description, cp.thumb_image, cocp.retail_price,cp.price
			FROM ".DBConsts::$CABINET_OPTIONAL_CABINET_PART." cocp, ".DBConsts::$CABINET_PART." cp 
			WHERE cocp.cabinet_code='".$cabinetCode."' and cocp.cabinet_part_code=cp.code";
		$result = DataProvider::Query($strSQL);
		if(mysqli_num_rows($result)==0)
			return null;
		$listImageArray = array();
		while($row= mysqli_fetch_array ($result,MYSQL_BOTH)){
			$imageArray = array("code"=>$row['code'], "name"=>$row['name'], "description"=>$row['description'], 
					"thumbimage"=>$row['thumb_image'], "retailprice"=>$row['retail_price'], "price"=>$row['price']);
			array_push($listImageArray, $imageArray);
		}
		return $listImageArray;
	}
	
	/**
	 * get products by category id.
	 * @param unknown $offset
	 * @param unknown $count
	 * @return NULL|multitype:
	 */
	public static function getProducts($page,$count){
		$offset = 0;
		if($page <= 0){
			return null;
		} else if($page > 0){
			$offset = ($page - 1) * $count;
		}
		
		$strSQL = "Select * from ".DBConsts::$CABINET." limit ".$offset.", ".$count;
		$products = DataProvider::Query($strSQL);		
		if(mysqli_num_rows($products)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($products,MYSQL_BOTH))
		{
			$array = array("code"=>$item['code'], "name"=>$item['name'], "short_name"=>$item['short_name'],"description"=>$item['description'],
					"short_description"=>$item['short_description'], "width"=>$item['width'], "height"=>$item['height'],"image"=>$item['image'],
					"retailprice"=>$item['retail_price'], "fobprice"=>$item['fob_price'],"cabinettypeid"=>$item['cabinet_type_id'],
					"isconfigurable"=>$item['is_configurable'],"gridthumbimage"=>$item['grid_thumb_image'],"listthumbimage"=>$item['list_thumb_image'],
					"specialprice"=>$item['special_price'],"membershipprice"=>$item['membership_price']);
			array_push($result, $array);
		}
		return $result;
	}
	
	/**
	 * get products by category id.
	 * @param unknown $offset
	 * @param unknown $count
	 * @return NULL|multitype:
	 */
	public static function getAllProducts(){
		$strSQL = "Select * from ".DBConsts::$CABINET ;
		$products = DataProvider::Query($strSQL);
		if(mysqli_num_rows($products)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($products,MYSQL_BOTH))
		{
			$array = array("code"=>$item['code'], "name"=>$item['name'], "short_name"=>$item['short_name'],"description"=>$item['description'],
					"short_description"=>$item['short_description'], "width"=>$item['width'], "height"=>$item['height'],"image"=>$item['image'],
					"retailprice"=>$item['retail_price'], "fobprice"=>$item['fob_price'],"cabinettypeid"=>$item['cabinet_type_id'],
					"isconfigurable"=>$item['is_configurable'],"gridthumbimage"=>$item['grid_thumb_image'],"listthumbimage"=>$item['list_thumb_image'],
					"specialprice"=>$item['special_price'],"membershipprice"=>$item['membership_price']);
			array_push($result, $array);
		}
		return $result;
	}
	
	/**
	 * get products by is_star = true.
	 * @return NULL|multitype:
	 */
	public static function getAllProductsByIsStar($isStar){
		$strSQL = "Select * from ".DBConsts::$CABINET." where is_star=".$isStar;
		$products = DataProvider::Query($strSQL);
		if(mysqli_num_rows($products)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($products,MYSQL_BOTH))
		{
			$array = array("code"=>$item['code'], "name"=>$item['name'], "short_name"=>$item['short_name'],"description"=>$item['description'],
					"short_description"=>$item['short_description'], "width"=>$item['width'], "height"=>$item['height'],"image"=>$item['image'],
					"retailprice"=>$item['retail_price'], "fobprice"=>$item['fob_price'],"cabinettypeid"=>$item['cabinet_type_id'],
					"isconfigurable"=>$item['is_configurable'],"gridthumbimage"=>$item['grid_thumb_image'],"listthumbimage"=>$item['list_thumb_image'],
					"specialprice"=>$item['special_price'],"membershipprice"=>$item['membership_price']);
			array_push($result, $array);
		}
		return $result;
	}
	
	/**
	 * get products by amount of top sold.
	 * @return NULL|multitype:
	 */
	public static function getAllProductsByHighestNumberSold($numberTopSold){
		$strSQL = "SELECT * FROM ".DBConsts::$CABINET." ORDER BY sold_number DESC limit ".$numberTopSold;
		$products = DataProvider::Query($strSQL);
		if(mysqli_num_rows($products)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($products,MYSQL_BOTH))
		{
			$array = array("code"=>$item['code'], "name"=>$item['name'], "short_name"=>$item['short_name'],"description"=>$item['description'],
					"short_description"=>$item['short_description'], "width"=>$item['width'], "height"=>$item['height'],"image"=>$item['image'],
					"retailprice"=>$item['retail_price'], "fobprice"=>$item['fob_price'],"cabinettypeid"=>$item['cabinet_type_id'],
					"isconfigurable"=>$item['is_configurable'],"gridthumbimage"=>$item['grid_thumb_image'],"listthumbimage"=>$item['list_thumb_image'],
					"specialprice"=>$item['special_price'],"membershipprice"=>$item['membership_price'],"soldnumber"=>$item['sold_number']);
			array_push($result, $array);
		}
		return $result;
	}
	/**
	 * get all slides
	 * @return NULL|multitype:
	 */
	public static function getAllSlides(){
		$strSQL = "SELECT id,description,url,slide_image, is_middle FROM ".DBConsts::$SLIDE." WHERE is_displayed=1";
		$slides = DataProvider::Query($strSQL);
		if(mysqli_num_rows($slides)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($slides,MYSQL_BOTH))
		{
			$array = array("id"=>$item['id'],"description"=>$item['description'],
					"url"=>$item['url'], "slideimage"=>$item['slide_image'], "ismiddle"=>$item['is_middle']);
			array_push($result, $array);
		}
		return $result;
	}
	
	/**
	 * get all banners
	 * @return NULL|multitype:
	 */
	public static function getAllBanners(){
		$strSQL = "SELECT id,description,url,banner_image FROM ".DBConsts::$BANNER." WHERE is_displayed=1";
		$slides = DataProvider::Query($strSQL);
		if(mysqli_num_rows($slides)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($slides,MYSQL_BOTH))
		{
			$array = array("id"=>$item['id'],"description"=>$item['description'],
					"url"=>$item['url'], "bannerimage"=>$item['banner_image']);
			array_push($result, $array);
		}
		return $result;
	}
	
	/**
	 * get all materials
	 * @return NULL|multitype:
	 */
	public static function getAllMaterials(){
		$strSQL = "SELECT id, name,material_image FROM ".DBConsts::$MATERIAL;
		$slides = DataProvider::Query($strSQL);
		if(mysqli_num_rows($slides)==0)
			return null;
		$result = array();
		while($item= mysqli_fetch_array ($slides,MYSQL_BOTH))
		{
			$array = array("id"=>$item['id'],"name"=>$item['name'],
					 "materialimage"=>$item['material_image']);
			array_push($result, $array);
		}
		return $result;
	}
	
}
?>